package com.shi.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.taglibs.standard.lang.jstl.AndOperator;

import com.shi.pojo.Exercises;
import com.shi.pojo.Role;
import com.shi.pojo.User;
public class ExercisesDao extends BaseDao {
	private Connection conn = null;
	private PreparedStatement stmt = null;
	private ResultSet rs = null;
	
	public int deleteExercise(int exerciseId) {
		String sql = "delete from exercises where exerciseId=" + exerciseId;
		return this.executeSQL(sql, null);
	}
	
	public Exercises findExerciseByUserId(String userId) {
		Exercises exercises = new Exercises();
		String sql = "select * from exercises, where userId=" + userId + "and isUser=true";
		try {
			conn = this.getConnection();
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			
			while (rs.next()) {
				
				exercises.setExercisesId(rs.getString("exercisesId"));
				exercises.setTitle(rs.getString("title"));
				exercises.setType(rs.getString("type"));
				exercises.setAnswer(rs.getString("answer"));
				exercises.setUserId(rs.getString("userId"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.closeResultSet(rs);
			this.closePreparedStatement(stmt);
			this.closeConnection(conn);
		}
		
		return exercises;
	}
	public Exercises findExerciseByType(String type) {
		Exercises exercises = new Exercises();
		String sql = "select * from exercises, where type =" + type + "and isUser=true";
		try {
			conn = this.getConnection();
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			
			while (rs.next()) {
				exercises.setExercisesId(rs.getString("exercisesId"));
				exercises.setTitle(rs.getString("title"));
				exercises.setType(rs.getString("type"));
				exercises.setAnswer(rs.getString("answer"));
				exercises.setUserId(rs.getString("userId"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.closeResultSet(rs);
			this.closePreparedStatement(stmt);
			this.closeConnection(conn);
		}
		return exercises;
	}
	public User queryByUsername(String username){
		String sql = "select * from myuser where username="+username;
		User user = new User();
		try {
			conn = this.getConnection();
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				user.setUserId(rs.getString("userId"));
				user.setUserName(rs.getString("username"));
				user.setPassword(rs.getString("password"));
			}
		}catch(Exception e){
			
		}finally {
			this.closeResultSet(rs);
			this.closePreparedStatement(stmt);
			this.closeConnection(conn);
		}
		return user;
	}
	public void addUser(User form) {
		String sql = "insert into myuser values(?,?,?,?,?,?)";
		String[] params = {form.getUserId(),form.getUserName(),form.getPassword(),form.getRegisterTime(),form.getRoleId(),form.getEmail()};
		this.executeSQL(sql, params);
	}

	public User queryByEmail(String email) {
		String sql = "select * from user where email="+email;
		User user = new User();
		try {
			conn = this.getConnection();
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				user.setUserName(rs.getString("username"));
			}
		}catch(Exception e){
			
		}finally {
			this.closeResultSet(rs);
			this.closePreparedStatement(stmt);
			this.closeConnection(conn);
		}
		return user;
	}

	public void addRole(Role role) {
		// TODO Auto-generated method stub
		String sql = "insert into role values(?,?)";
		String[] params = {role.getRoleId(),role.getRoleName()};
		this.executeSQL(sql, params);
	}

	public void addExercise(String id, String title, String type, String a,
			String b, String c, String d, String answer, String uploadUserId) {
		String sql = "insert into exercises(exercisesId, title,type,a,b,c,d,answer,uploadUserId) values(?,?,?,?,?,?,?,?,?)";
		String[] params = { id,title,type,a,b,c,d,answer,uploadUserId};
		this.executeSQL(sql, params);
	}

	public List<Exercises> queryExercisesById(List<Exercises> form) throws Exception {
		List<Exercises> exercises = new ArrayList<Exercises>();
		
		try {
			conn = this.getConnection();
			for(Exercises exercise : form){
					String sql = "select * from exercises where exercisesId="+ "'" + exercise.getExercisesId() + "'";
				
					Exercises e = new Exercises();
					stmt = conn.prepareStatement(sql);
					rs = stmt.executeQuery();
					while(rs.next()) {
						e.setExercisesId(rs.getString("exercisesId"));
						e.setAnswer(rs.getString("answer"));
						exercises.add(e);
					}
				}
		}
		catch(Exception e){
			throw new Exception("未知错误");
		}
		finally {
			this.closeResultSet(rs);
			this.closePreparedStatement(stmt);
			this.closeConnection(conn);
		}
		return exercises;
	}

	public void addWrongExercise(String exercisesId, String userId, String wrongAnswer, String answer, String title) {
		// TODO Auto-generated method stub
		String sql = "insert into wrongexercise values(?,?,?,?,?)";
		String[] params = {exercisesId,userId,wrongAnswer,answer,title};
		this.executeSQL(sql, params);
	}

	public int findExercisesSize() {
		int i = 0;
		try {
		
			String sql = "select  count(*)  from exercises";
			conn = this.getConnection();
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				i = rs.getInt(1);
			}
		}
		catch(Exception e){
			
		}
		finally {
			this.closeResultSet(rs);
			this.closePreparedStatement(stmt);
			this.closeConnection(conn);
		}
		return i;
	}

	public List<Exercises> findExercises(int start, int end) {
		// TODO Auto-generated method stub
		List<Exercises> exercises = new ArrayList<Exercises>();
		try {
				String sql = "select * from exercises limit "+start + " , 10";
				conn = this.getConnection();
				stmt = conn.prepareStatement(sql);
				rs = stmt.executeQuery();
				while (rs.next()) {
					Exercises exercise = new Exercises();
					exercise.setExercisesId(rs.getString("exercisesId"));
					exercise.setTitle(rs.getString("title"));
					exercise.setA(rs.getString("A"));
					exercise.setB(rs.getString("B"));
					exercise.setC(rs.getString("C"));
					exercise.setD(rs.getString("D"));
					exercises.add(exercise);
				}
		}
		catch(Exception e){
			
		}
		finally {
			this.closeResultSet(rs);
			this.closePreparedStatement(stmt);
			this.closeConnection(conn);
		}
		return exercises;
	}

	public List<Exercises> findWrongExerciseByUser(String userId) {
		// TODO Auto-generated method stub
				List<Exercises> exercises = new ArrayList<Exercises>();
				try {
						String sql = "select * from wrongexercise where userId="+ "'" +userId + "'";
						conn = this.getConnection();
						stmt = conn.prepareStatement(sql);
						rs = stmt.executeQuery();
						while (rs.next()) {
							Exercises exercise = new Exercises();
							exercise.setExercisesId(rs.getString("exercisesId"));
							exercise.setTitle(rs.getString("title"));
							exercises.add(exercise);
						}
				}
				catch(Exception e){
					
				}
				finally {
					this.closeResultSet(rs);
					this.closePreparedStatement(stmt);
					this.closeConnection(conn);
				}
				return exercises;
	}

	public Exercises findWrongExerciseByExerciseId(String exercisesId,String userId) {
		Exercises exercise = null;
		try {
			String sql = "select * from wrongexercise w,exercises e where w.exercisesId=e.exercisesId and w.exercisesId='" +exercisesId + "' " + "and w.userId='" + userId + "'";
			conn = this.getConnection();
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				exercise = new Exercises();
				exercise.setExercisesId(rs.getString("exercisesId"));
				exercise.setTitle(rs.getString("title"));
				exercise.setA(rs.getString("A"));
				exercise.setB(rs.getString("B"));
				exercise.setC(rs.getString("C"));
				exercise.setD(rs.getString("D"));
				exercise.setAnswer(rs.getString("answer"));
				exercise.setMyWrongAnswer("wronganswer");
			}
		}
		catch(Exception e){
			
		}
		finally {
			this.closeResultSet(rs);
			this.closePreparedStatement(stmt);
			this.closeConnection(conn);
		}
		return exercise;
	}

	public int addExerciseByExerciseIdAndUserId(String exercisesId,
			String userId) {
		// TODO Auto-generated method stub
		String sql = "insert into exercisescollection values(?,?)";
		String[] params = {exercisesId,userId};
		return this.executeSQL(sql, params);
	}

	public List<Exercises> findMyExercisesByUserId(String userId) {
		List<Exercises> exercises = new ArrayList<Exercises>();
		try {
				String sql = "select * from exercisescollection ec,exercises e where ec.exercisesId=e.exercisesId and ec.userId="+ "'" +userId + "'";
				conn = this.getConnection();
				stmt = conn.prepareStatement(sql);
				rs = stmt.executeQuery();
				while (rs.next()) {
					Exercises exercise = new Exercises();
					exercise.setExercisesId(rs.getString("exercisesId"));
					exercise.setTitle(rs.getString("title"));
					exercise.setA(rs.getString("A"));
					exercise.setB(rs.getString("B"));
					exercise.setC(rs.getString("C"));
					exercise.setD(rs.getString("D"));
					exercise.setAnswer(rs.getString("answer"));
					exercises.add(exercise);
				}
		}
		catch(Exception e){
			
		}
		finally {
			this.closeResultSet(rs);
			this.closePreparedStatement(stmt);
			this.closeConnection(conn);
		}
		return exercises;
	}

	public int removeExerciseByExerciseIdAndUserId(String exercisesId,
			String userId) {
		int i = 0;
		String sql = "delete from exercisescollection where exercisesId=" + "?" + " and userId=" + "?";
		String[] params = {exercisesId,userId};
		return this.executeSQL(sql, params);
	}
}
